package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import entity.tDevice;

public final class DBUtils
{
    private static String sqlurl = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=greentruss";
    
    private static String user = "sa";
    
    private static String password = "1qaz2wsx3edc0okm";
    
    private static Connection sqlServerConn = null;
    
    public static Connection getSqlServerConn()
        throws SQLException
    {
        return DriverManager.getConnection(sqlurl, user, password);
    }
    
    // �ͷ�����
    public static void free(ResultSet rs, PreparedStatement ps, Connection conn)
    {
        try
        {
            if (rs != null)
            {
                rs.close();
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        finally
        {
            try
            {
                if (ps != null)
                {
                    ps.close();
                }
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
            finally
            {
                
                try
                {
                    if (conn != null)
                    {
                        conn.close();
                    }
                }
                catch (SQLException e)
                {
                    e.printStackTrace();
                }
                
            }
            
        }
        
    }
    
    public List<tDevice> getAllDevice()
        throws SQLException
    {
        List<tDevice> list = new ArrayList<tDevice>();
        ResultSet rs = null;
        Statement sqlServerPs = null;
        try
        {
            sqlServerConn = DBUtils.getSqlServerConn();
            
            sqlServerPs = sqlServerConn.createStatement();
            String sql = "select deviceId,mac from tDevice";
            rs = sqlServerPs.executeQuery(sql);
            
            while (rs.next())
            {
                tDevice td = new tDevice();
                td.setDeviceId(rs.getString("deviceId"));
                td.setMac(rs.getString("mac"));
                list.add(td);
                // System.out.println(td.getDeviceId()+","+td.getMac());
            }
            
        }
        catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally
        {
            rs.close();
            sqlServerPs.close();
            sqlServerConn.close();
        }
        return list;
        
    }
    
    // ������
    static
    {
        try
        {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        }
        catch (ClassNotFoundException e)
        {
            System.out.println("����س���");
        }
    }
    
}